<?php
if (!defined("BASEPATH")) exit("No direct script access allowed");

class City extends MY_Model {

	function __construct() {
		parent::__construct();
		$this->tableName = "city";
		$this->prefix = "city";
	}

	function getCity($where = NULL, $start = NULL, $limit = NULL, $fields = NULL, $query = NULL, $order = "cityName", $dir = "ASC") {

		$fieldsQuery = Common::fieldsquery($fields, $query);
		$order = Common::coalesce(array($order, "cityName"));
		$dir = Common::coalesce(array($dir, "ASC"));

		$qs =	"
					select
						city_id, city_name, city_url_name, city_cou_id, cou_name, cou_url_name, city_priority, city_latitude, city_longitude, city_presenter, city_data, if(city_data = 'N' and city_presenter = 'I', '".$this->db->escape_str($this->lang->line("YES"))."', '') as city_data2, con_name, con_url_name, t.e2i_image_name, t.e2i_alt, t2.e2c_temp_flag
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0

						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin() and !defined("OVERRIDE"))?
						(" join ".$this->db->dbprefix."usr2ent on u2e_del = 0 and u2e_usr_id = '".$this->session->userdata("usrId")."' and (u2e_cou_id = cou_id or u2e_city_id = city_id) "):(""))."

						left join ".$this->db->dbprefix."institute on city_id = ins_city_id and ins_del = 0
						left join ".$this->db->dbprefix."major on ins_id = maj_ins_id and maj_del = 0
						left join ".$this->db->dbprefix."ent2cat on e2c_entity = 'CITY' and city_id = e2c_ent_id and e2c_del = 0 and coalesce(e2c_int, e2c_text) is not null
						left join (
							select
								e2i_city_id, e2i_image_name, e2i_alt
							from
								".$this->db->dbprefix."ent2image
							where
								e2i_city_id is not null and e2i_del = 0
							order by
								rand()
						) as t on city_id = t.e2i_city_id
						left join (
							select
								e2c_ent_id, e2c_temp_flag
							from
								".$this->db->dbprefix."ent2cat 
							where
								e2c_entity = 'CITY' and e2c_del = 0 and e2c_temp_flag = 'I'
							group by
								e2c_ent_id
						) as t2 on t2.e2c_ent_id = city_id
					where
						city_del = 0 ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
					group by
						city_id
					order by
						".(($order == "rand()")?($order):(Common::underscore($order)." ".$dir))."
					".((is_numeric($start))?(" limit ".$start.",".$limit):(""))."
						";
		$result = $this->db->query($qs);

		$getCity = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {

				$thumb = preg_split("/\./", $row->e2i_image_name);
				$len = strlen($thumb[count($thumb)-1]);

				$getCity[] = array(
					"cityId" => $row->city_id,
					"cityName" => $row->city_name,
					"cityUrlName" => $row->city_url_name,
					"cityCouId" => $row->city_cou_id,
					"couName" => $row->cou_name,
					"conName" => $row->con_name,
					"cityPriority" => $row->city_priority,
					"couUrlName" => $row->cou_url_name,
					"conUrlName" => $row->con_url_name,
					"cityImageName" => $row->e2i_image_name,
					"cityImageNameThumb" => (($row->e2i_image_name)?(substr($row->e2i_image_name, 0, -($len+1))."_thumb.".$thumb[count($thumb)-1]):("")),
					"cityLatitude" => $row->city_latitude,
					"cityLongitude" => $row->city_longitude,
					"cityImageAlt" => (($row->e2i_alt)?($row->e2i_alt):("")),
					"e2cTempFlag" => $row->e2c_temp_flag,
					"cityPresenter" => $row->city_presenter,
					"cityData" => $row->city_data,
					"cityData2" => $row->city_data2,
				);
			}

		}

		return $getCity;
	}

	function getCityCount($where = NULL, $fields, $query) {

		$fieldsQuery = Common::fieldsquery($fields, $query);

		$qs =	"
					select
						count(*) as cnt
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0

						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin() and !defined("OVERRIDE"))?
						(" join ".$this->db->dbprefix."usr2ent on u2e_del = 0 and u2e_usr_id = '".$this->session->userdata("usrId")."' and (u2e_cou_id = cou_id or u2e_city_id = city_id) "):(""))."
						
					where
						city_del = 0 ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
					";
		$result = $this->db->query($qs);

		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				return $row->cnt;
			}
		}
	}

}